from Accounting.Entity.Tree import EntityTree
from DataAccess.DBConnFactory import DBConnFactory
from string import Template
import timeit

def daily_clearing(ref_date):
	print 'daily_clearing...'
	start = timeit.default_timer()

	acct_conn = DBConnFactory().get_db_connection('ACCOUNTING')
	acct_cursor = acct_conn.cursor()
	
	sql_tpl = Template('''select mi.master_id,mv.mkt_val,mv.currency
					from portfolio_mkt_val@prd_pke mv
					join master_info mi
						on mi.portfolio_id=mv.portfolio_id
					where mv.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')''')
	acct_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat())) 	
	r = acct_cursor.fetchall()
	for item in r:
		master_id = item[0]
		portf_mkt_val = item[1]
		curncy = item[2]
		
		master = EntityTree().find_node(master_id)
		if master:
			master.settlement(ref_date, portf_mkt_val, curncy)			
		else:
			raise RuntimeError(), ('Master entity not exist, id='+master_id)
	
	elapsed = (timeit.default_timer() - start)
	print 'daily_clearing complete, ', elapsed

	
def reflect_to_portf_cash_account(ref_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	acct_conn = DBConnFactory().get_db_connection('ACCOUNTING')
	acct_cursor = acct_conn.cursor()
	
	#update to portfolio cash account
	sql_tpl = Template('''select * from (
						select cc.entity,sum(cc.amount) amount,cc.curncy
						from cash_chg cc
						join master_info mi
						  on mi.master_id=cc.entity
						  and cc.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						  and cc.detail<>'m2m_adj'
						  and cc.detail<>'others' 
						  and cc.detail<>'av_adj' 
						group by cc.entity,cc.curncy) 
						where amount<>0''')
	acct_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	#print 'debug, cash_chg=', acct_cursor.fetchall() , ref_date.isoformat() 
	r = [(ref_date.isoformat(),)+item for item in acct_cursor.fetchall()]
	#print 'debug, cash_chg=', r , ref_date.isoformat()
	pke_cursor.executemany('''insert into cash_switch
							values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)''', r)
	pke_conn.commit()
		
	sql_tpl = Template('''update 
					(select /*+BYPASS_UJVC*/ cs.amount as cf,pos.amount
					from cash_switch cs
					join position pos 
						on pos.ref_date=cs.ref_date 
						and pos.portfolio_id=cs.portfolio_id 
						and pos.price_currency=cs.currency
						and pos.security_type='CASH' 
						and pos.ticker='9999999'
					where cs.ref_date=TO_DATE('${DATE}','yyyy-mm-dd'))
					set amount=amount+cf
					''')   
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	pke_conn.commit()
	
	
	
def clear_daily_record(ref_date):
	conn = DBConnFactory().get_db_connection('ACCOUNTING')

	sql_tpl = Template('''delete from asset_value where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')''')
	conn.cursor().execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	sql_tpl = Template('''delete from cash_chg where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')''')
	conn.cursor().execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	sql_tpl = Template('''delete from fee where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')''')
	conn.cursor().execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	sql_tpl = Template('''delete from share_chg where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')''')
	conn.cursor().execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	conn.commit()
	


